package StudentAchievementManagementSystem.component;

import StudentAchievementManagementSystem.mysql.MysqlUtil;
import StudentAchievementManagementSystem.ui.LoginScreen;

import javax.swing.*;
import java.awt.*;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

public class test {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {

        String major="计算机科学";
        String class_="001";
        Connection connection = MysqlUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql1 = "select  count(student.sno) from studentcoursegrade,student where (student.major='"+major+"'and student.class_='"+class_+"' and  studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 60 and 70)";
        String sql2 = "select  count(student.sno) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 60 and 70) ";
        String sql3 = "select  count(student.sno)from student,studentcoursegrade  where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno and studentcoursegrade.grade between 70 and 80) ";
        String sql4 = "select  count(student.sno)from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 80 and 90) ";
        String sql5 = "select  count(student.sno) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 90 and 100) ";
        Vector<String> vector =new Vector<>();
        ResultSet resultSet1 = statement.executeQuery(sql1);
        while(resultSet1.next()){
            vector.add(resultSet1.getString(1));
        }

        ResultSet resultSet2 = statement.executeQuery(sql2);
        while(resultSet2.next()){
            vector.add(resultSet2.getString(1));
        }
        ResultSet resultSet3 = statement.executeQuery(sql3);
        while(resultSet3.next()){
            vector.add(resultSet3.getString(1));
        }
        ResultSet resultSet4 = statement.executeQuery(sql4);
        while(resultSet4.next()){
            vector.add(resultSet4.getString(1));
        }
        ResultSet resultSet5 = statement.executeQuery(sql5);
        while(resultSet5.next()){
            vector.add(resultSet5.getString(1));
        }
        System.out.println(vector);

    }

//        String sno="s003";String cno ="c005"; String  grade="86";
//        Connection connection = MysqlUtil.getConnection();
//        Statement statement = connection.createStatement();
//        String sql = "insert into studentcoursegrade(sno,cno,grade) select '"+sno+"','"+cno+"','"+grade+"' from dual where exists(select sno from student where sno='"+sno+"') and exists(select cno from course where cno='"+cno+"')and not exists(select sno,cno  from studentcoursegrade where cno='"+cno+"'and sno ='"+sno+"')";
//        statement.execute(sql);
//        String Sqltest="select grade from studentcoursegrade where sno='"+sno+"' and cno='"+cno+"' ";
////        statement.execute(Sqltest)
//        ResultSet resultSet = statement.executeQuery(Sqltest);
////
//        while (resultSet.next()){
//            if ( resultSet.getString(1).equals(grade)) System.out.println("成功");
//        }
//        Connection connection = MysqlUtil.getConnection();
//        Statement statement = connection.createStatement();
//        String sql = "select student.sno,sname,major,course.cname,grade from student ,studentcoursegrade,course where student.sno=studentcoursegrade.sno and course.cno=studentcoursegrade.cno";
//        Vector<Vector> vectors =new Vector<>();
//        ResultSet resultSet = statement.executeQuery(sql);
//        while(resultSet.next()){
//            Vector<String> vector1 = new Vector < String > ();
//            for (int i=1;i<6;i++){
//                vector1.add(resultSet.getString(i));
//            }
//            try{
//                vectors.add(vector1);
////                vector1.clear();
//            }catch (NullPointerException e){
//                e.printStackTrace();
//            }
//        }
//        System.out.println(vectors);
//    }
//    public static Vector<Vector> convertResultInfoData2Vector(ResultInfo info){
//        List list = (List) info.getData();
//        Vector<Vector> vectors = new Vector<>();
//        for (int i = 0; i < list.size(); i++) {
//            HashMap map = (HashMap) list.get(i);
//            Vector vector = new Vector();
//            vector.addAll(map.values());
//            vectors.add(vector);
//        }
//        return vectors;
//    }
    //请求数据
//    public void requestData(){
//        GetUtils.get("http://localhost:8080/book/findAllBook", new SuccessListener() {
//            @Override
//            public void success(String result) {
//                ResultInfo info = JsonUtils.parseResult(result);
//                Vector<Vector> vectors = ResultInfoData2Vector.convertResultInfoData2Vector(info);
//
//                //清空tableData的数据
//                tableData.clear();
//                for (Vector vector : vectors) {
//                    tableData.add(vector);
//                }
//
//                //刷新表格
//                tableModel.fireTableDataChanged();
//            }
//        }, new FailListener() {
//            @Override
//            public void fail() {
//
//            }
//        });
//    }
}
